from django.shortcuts import render
from .models import Student, Score
from django.http import HttpRequest, HttpResponse
from django.template.loader import get_template
from django.template.backends.django import Template
import datetime
from django.db.models import Q, Avg, Sum, Max, Min, Count


stu_mgr = Student.objects
sco_mgr = Score.objects

def students(request):
    total_score = sco_mgr.values('name').annotate(Sum('score')).order_by('-score__sum')
    names = [list(i.values())[0] for i in total_score]
    scores = [list(i.values())[1] for i in total_score]
    failed_count = sco_mgr.filter(score__lt=60).values('name').annotate().count()
    pass_count = stu_mgr.count() - failed_count
    score_lt60_count_list =[list(i.values())[1] for i in sco_mgr.filter(score__lt=60).values('exam_subjects').annotate(Count('exam_subjects'))]
    score_let60_get80_count_list = [list(i.values())[1] for i in sco_mgr.filter(score__gte=60).filter(score__lte=80).values('exam_subjects').annotate(Count('exam_subjects'))]
    score_gt80_count_list = [list(i.values())[1] for i in sco_mgr.filter(score__gt=80).values('exam_subjects').annotate(Count('exam_subjects'))]
    students_data = {
        'names':names,
        'scores':scores,
        'failed_count':failed_count,
        'pass_count':pass_count,
        'score_lt60_count_list':score_lt60_count_list,
        'score_let60_get80_count_list':score_let60_get80_count_list,
        'score_gt80_count_list':score_gt80_count_list,

    }

    context = {'students_data': students_data}
    return render(request, 'index.html', context)



